# -*- coding: utf-8 -*-
"""
Exporter Sqlite
Exporter MySQL SQL 文件
"""
from scrapy.exporters import BaseItemExporter
from scrapy.utils.python import to_bytes

import sqlite3


class SqliteItemExporter(BaseItemExporter):
    
    def __init__(self, file, **kwargs):
        self._configure(kwargs)
        self.conn = sqlite3.connect(file.name)
        self.conn.text_factory = str
        self.created_tables = []
    
    def export_item(self, item):        
        item_class_name = type(item).__name__.lower()
        
        if item_class_name not in self.created_tables:
            keys = None
            self._create_table(item_class_name, item.fields.iterkeys())
            self.created_tables.append(item_class_name)
        
        field_list = []
        value_list = []
        for field_name in item.iterkeys():
            field_list.append('[%s]' % field_name)
            field = item.fields[field_name]
            value_list.append(self.serialize_field(field, field_name, item[field_name]))
        
        sql = 'insert or ignore into [%s] (%s) values (%s)' % (item_class_name, ', '.join(field_list), ', '.join(['?' for f in field_list]))
        self.conn.execute(sql, value_list)
        self.conn.commit()
            
    def _create_table(self, table_name, columns, keys = None):
        sql = 'create table if not exists [%s] ' % table_name
        
        column_define = ['[%s] text' % column for column in columns]
        if keys:
            if len(keys) > 0:
                primary_key = 'primary key (%s)' % ', '.join(keys[0])
                column_define.append(primary_key)
                
            for key in keys[1:]:
                column_define.append('unique (%s)' % ', '.join(key))
        
        sql += '(%s)' % ', '.join(column_define)
        
        print 'sql: %s' % sql
        self.conn.execute(sql)
        self.conn.commit()
        
    def __del__(self):
        self.conn.close()

# -------------------------------------------------- End class SqliteItemExporter

class MySQLItemExporter(BaseItemExporter):
    
    def __init__(self, file, **kwargs):
        self._configure(kwargs)
        self.file = file
        self.created_tables = []
    
    def export_item(self, item):        
        item_class_name = type(item).__name__.lower()
        
        if item_class_name not in self.created_tables:
            keys = None
            self._create_table(item_class_name, item.fields.iterkeys())
            self.created_tables.append(item_class_name)
        
        #field_list = []
        #value_list = []
        #for field_name in item.iterkeys():
            #field_list.append('`%s`' % field_name)
            #field = item.fields[field_name]
            #value_list.append(self.serialize_field(field, field_name, item[field_name]))
        
        itemdict = dict(self._get_serialized_fields(item))

        try:
            sql = '''INSERT INTO `%s` (`%s`) values ("%s");''' % \
                    (item_class_name, '`, `'.join(itemdict.keys()), '", "'.join(itemdict.values()))
            self.file.write(to_bytes(sql) + '\n')
        except:
            print(itemdict)
            
    def _create_table(self, table_name, columns, keys = None):
        sql = '''DROP TABLE IF EXISTS `%s`;
CREATE TABLE IF NOT EXISTS `%s` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
''' % (table_name, table_name)
        
        column_define = ['`%s` text' % column for column in columns]

        sql += ',\n'.join(column_define)
        sql += ''',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
'''
        
        self.file.write(to_bytes(sql) + '\n')
        
# -------------------------------------------------- End class MySQLItemExporter

# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4
